package database;

import contents.entities.SubstitutionToSolve;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.ParseException;
import java.util.List;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Map;
import java.util.HashMap;

public class Database {
    public enum UserRole {
        STUDENT, PARENT, TEACHER, ADMIN
    }
    
    private static Connection connection;
    
    public static synchronized void connect() {
        try {
            Class.forName(DatabaseSetting.DRIVER_CLASS);
            connection = DriverManager.getConnection(DatabaseSetting.URL,
                         DatabaseSetting.USER, DatabaseSetting.PASSWORD);
            connection.setAutoCommit(true);
            System.out.println("Connected to database.");
        } catch (ClassNotFoundException exception) {
            System.err.println("Exception occured during connecting to database: " + exception.getMessage());
        } catch (SQLException exception) {
            System.err.println("Exception occured during connecting to database: " + exception.getMessage());
        }
    }
    
    public static synchronized void ensureConnectionIsOpen() {
        try {
            if (connection.isClosed() || !connection.isValid(15)) {
                connection.close();
                
                Class.forName(DatabaseSetting.DRIVER_CLASS);
                connection = DriverManager.getConnection(DatabaseSetting.URL,
                             DatabaseSetting.USER, DatabaseSetting.PASSWORD);
                connection.setAutoCommit(true);
                System.out.println("Reconnected to database.");
            }
        } catch (ClassNotFoundException exception) {
            System.err.println("Exception occured during reconnecting to database: " + exception.getMessage());
        } catch (SQLException exception) {
            System.err.println("Exception occured during reconnecting to database: " + exception.getMessage());
        }
    }
        
    public static synchronized void disconnect() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
                System.out.println("Disconnected from database.");
            }
        } catch (SQLException exception) {
            System.err.println("Exception occured during disconnecting from database: " + exception.getMessage());
        }
    }
    
    public static synchronized String selectUserRoleOfUsernameAndPassword(String username, String passwordHash) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_USER_ROLE_OF_USERNAME_AND_PASSWORD);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, passwordHash);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            resultSet.close();
            preparedStatement.close();
            return "visitor";
        } else {
            String role = resultSet.getString(1);
            resultSet.close();
            preparedStatement.close();
            
            if (role.equals("A")) {
                return "admin";
            } else if (role.equals("S")) {
                return "student";
            } else if (role.equals("T")) {
                return "teacher";
            } else if (role.equals("P")) {
                return "parent";
            }
        }
        // this should never execute
        return "visitor";
    }
    
    public static synchronized void logAccess(String IPAddress, String username, String passwordHash, boolean success) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_TO_ACCESS_LOG);
        preparedStatement.setString(1, IPAddress);
        preparedStatement.setString(2, username);
        preparedStatement.setString(3, passwordHash);
        
        java.util.Date currentDate = new java.util.Date();
        java.text.SimpleDateFormat datetimeFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        preparedStatement.setString(4, datetimeFormat.format(currentDate));
        
        if (success) {
            preparedStatement.setInt(5, 1);
        } else {
            preparedStatement.setInt(5, 0);
        }
        
        preparedStatement.execute();
        preparedStatement.close();
    }
    
    public static synchronized Integer selectUserId(String username, UserRole role) throws SQLException, IllegalArgumentException {
        Integer userId;
       
        PreparedStatement preparedStatement;
        if (role == UserRole.STUDENT) {
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_STUDENT_ID);
        } else if (role == UserRole.PARENT) {
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_PARENT_ID);
        } else if (role == UserRole.TEACHER) {
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TEACHER_ID);
        } else {
            // ADMIN
            throw new IllegalArgumentException("selectUserId: Admin has no ID.");
        }
        preparedStatement.setString(1, username);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        userId = Integer.valueOf(resultSet.getInt(1));
        resultSet.close();
        preparedStatement.close();
        
        return userId;
    }
    
    public static synchronized DatabaseEntities.Student selectNameClassIdAndClassNameOfStudent(int studentId) throws SQLException {
        DatabaseEntities.Student student = new DatabaseEntities.Student();
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_STUDENT_NAME_CLASS_ID_AND_CLASS_NAME);
        preparedStatement.setInt(1, studentId);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        student.name = resultSet.getString(1) + " " + resultSet.getString(2);
        student.className = resultSet.getString(3);
        student.classId = resultSet.getInt(4);
        resultSet.close();
        preparedStatement.close();
        
        return student;
    }
    
    public static synchronized DatabaseEntities.Teacher selectNameOfTeacher(int teacherId) throws SQLException {
        DatabaseEntities.Teacher teacher = new DatabaseEntities.Teacher();
            
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TEACHER_NAME_AND_USERNAME);
        preparedStatement.setInt(1, teacherId);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        teacher.name = resultSet.getString(1) + " " + resultSet.getString(2);
        resultSet.close();
        preparedStatement.close();
           
        return teacher;
    }
    
    public static synchronized DatabaseEntities.Parent selectNameStudentNameClassIdAndClassNameOfParent(int parentId) throws SQLException {
        DatabaseEntities.Parent parent = new DatabaseEntities.Parent();
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_STUDENT_NAME_PARENT_NAME_CLASS_ID_AND_CLASS_NAME);
        preparedStatement.setInt(1, parentId);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        parent.name = resultSet.getString(4) + " " + resultSet.getString(5);
        parent.studentName = resultSet.getString(1) + " " + resultSet.getString(2);
        parent.className = resultSet.getString(3);
        parent.classId = resultSet.getInt(6);
        resultSet.close();
        preparedStatement.close();
        
        return parent;
    }
    
    public static synchronized String selectUsernameOfUser(UserRole role, int userId) throws SQLException {
        PreparedStatement preparedStatement;         
        switch (role) {
            case STUDENT:
                preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_USERNAME_OF_STUDENT);
                break;
            case PARENT:
                preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_USERNAME_OF_PARENT);
                break;
            case TEACHER:
                preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_USERNAME_OF_TEACHER);
                break;
            default:
                return "admin";
        }
        preparedStatement.setInt(1, userId);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        String username;
        if (!resultSet.next()) {
            username = null;
        } else {
            username = resultSet.getString(1);
        }
        resultSet.close();
        preparedStatement.close();
        return username;
    }

    public static synchronized Map<Integer, contents.entities.Class> selectClasses() throws SQLException {
        Map<Integer, contents.entities.Class> classes = new HashMap<Integer, contents.entities.Class>();
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_CLASSES);
        ResultSet resultSet = preparedStatement.executeQuery();  
        int i=1;
        while(resultSet.next()){
            classes.put(i, new contents.entities.Class(resultSet.getInt(1),resultSet.getString(2)));
            i++;
        }
        resultSet.close();
        preparedStatement.close();
        return classes; 
    }
    
    public static synchronized Map<Integer, contents.entities.Subject> selectSubjects() throws SQLException {
        Map<Integer, contents.entities.Subject> subjects = new HashMap<Integer, contents.entities.Subject>();
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBJECTS);
        ResultSet resultSet = preparedStatement.executeQuery();  
        int i=1;
        while(resultSet.next()){
            subjects.put(i, new contents.entities.Subject(resultSet.getInt(1),resultSet.getString(2),Integer.valueOf(resultSet.getInt(3))));
            i++;
        }
        resultSet.close();
        preparedStatement.close();
        return subjects; 
    }
    
    public static synchronized Map<Integer, contents.entities.Student> selectStudentsOfClass(int classId) throws SQLException {
        Map<Integer, contents.entities.Student> students = new HashMap<Integer, contents.entities.Student>();
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_STUDENTS_OF_CLASS);
        preparedStatement.setInt(1, classId);
        ResultSet resultSet = preparedStatement.executeQuery();  
        int i=1;
        while(resultSet.next()) {
            contents.entities.Parent parent = Database.getParent(resultSet.getInt(5));
            String className = Database.selectClassName(resultSet.getInt(6));
            contents.entities.Class schoolClass = new contents.entities.Class(resultSet.getInt(6), className);
            students.put(i, new contents.entities.Student(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3), resultSet.getString(4), 
                    parent, schoolClass));
            i++;
        }
        resultSet.close();
        preparedStatement.close();
        return students;
    }
    
    public static synchronized contents.entities.Parent getParent(int parentId) throws SQLException {       
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_PARENT_NAME_AND_USERNAME);
        preparedStatement.setInt(1, parentId);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        
        String firstName = resultSet.getString(1);
        String lastName = resultSet.getString(2);
        String username = resultSet.getString(3);
        
        resultSet.close();
        preparedStatement.close();
                
        return new contents.entities.Parent(parentId, firstName, lastName, username);
    }
 
    public static synchronized String selectClassName(int classId) throws SQLException {
        String className;
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_CLASS_NAME);
        preparedStatement.setInt(1, classId);
        ResultSet resultSet = preparedStatement.executeQuery();  
        resultSet.next();
        className= resultSet.getString(1);
        resultSet.close();
        preparedStatement.close();
        return className;
    }   
   
    public static synchronized Map<Integer, contents.entities.Teacher> selectTeachers() throws SQLException {
        Map<Integer, contents.entities.Teacher> teachers = new HashMap<Integer, contents.entities.Teacher>();
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TEACHERS);
        ResultSet resultSet = preparedStatement.executeQuery();  
        int i=1;
        while(resultSet.next()){
            List<contents.entities.Subject> subjects = Database.selectSubjectsOfTeacher(resultSet.getInt(1));
            teachers.put(i,new contents.entities.Teacher(resultSet.getInt(1),resultSet.getString(2), resultSet.getString(3),
                    resultSet.getString(4), subjects));
            i++;
        }
        resultSet.close();
        preparedStatement.close();
        return teachers;
    }
    
    public static synchronized List<contents.entities.Subject> selectSubjectsOfTeacher(int teacherId) throws SQLException {
        List<contents.entities.Subject> subjects = new ArrayList<contents.entities.Subject>();
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBJECTS_OF_TEACHER);
        preparedStatement.setInt(1, teacherId);
        ResultSet resultSet = preparedStatement.executeQuery(); 
        while(resultSet.next()){
            subjects.add(new contents.entities.Subject(resultSet.getInt(1), resultSet.getString(2), resultSet.getInt(3)) ); 
        }
        resultSet.close();
        preparedStatement.close();
        return subjects;
    }
         
    public static synchronized boolean updateUserPassword(String username, String oldPasswordHash, String newPasswordHash) throws SQLException {       
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_UPDATE_PASSWORD_OF_USERNAME_WITH_PASSWORD);
        preparedStatement.setString(1, newPasswordHash);
        preparedStatement.setString(2, username);
        preparedStatement.setString(3, oldPasswordHash);
        
        int rowsUpdated = preparedStatement.executeUpdate();
        preparedStatement.close();
        if (rowsUpdated != 1) {
            return false;
        } else {
            return true;
        }
    }
    
    public static synchronized void setMessagesOfUserAsSeen(String username) throws SQLException {       
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_UPDATE_MESSAGES_OF_USERNAME_TO_SEEN);
        preparedStatement.setString(1, username);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    
    public static synchronized List getMessagesOfUser(String username) throws SQLException {
        List messages = new ArrayList<DatabaseEntities.Message>();
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_MESSAGES_OF_USERNAME);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, username);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        DatabaseEntities.Message message;
        while (resultSet.next()) {
            message = new DatabaseEntities.Message();
            
            message.messageId = resultSet.getInt(1);
            message.timestamp = resultSet.getString(4);
            message.text = resultSet.getString(5);
                        
            // first get fromUsername
            String remotePeerUsername = resultSet.getString(2);
            if (!remotePeerUsername.equals(username)) {
                // current user is receiver
                message.sender = DatabaseEntities.Message.Sender.REMOTE_PEER;
                // don't continue if this message was deleted
                int to_deleted = resultSet.getInt(7);
                if (to_deleted == 1) {
                    continue;
                }
            } else {
                // current user is sender
                message.sender = DatabaseEntities.Message.Sender.ME;
                
                // else get toUsername
                remotePeerUsername = resultSet.getString(3);
                
                // don't continue if this message was deleted
                int from_deleted = resultSet.getInt(6);
                if (from_deleted == 1) {
                    continue;
                }
            }
                
            String remotePeerUserRole = selectRoleOfUsername(remotePeerUsername);
            int remotePeerUserId;
            String remotePeerFirstName, remotePeerLastName;
            if (remotePeerUserRole.equals("student")) {
                remotePeerUserId = selectUserId(remotePeerUsername, UserRole.STUDENT);
                // get name of peer (student)
                remotePeerFirstName = selectFirstName(remotePeerUserId, UserRole.STUDENT);
                remotePeerLastName = selectLastName(remotePeerUserId, UserRole.STUDENT);
                
            } else if (remotePeerUserRole.equals("parent")) {
                remotePeerUserId = selectUserId(remotePeerUsername, UserRole.PARENT);
                // get name of peer (parent)
                remotePeerFirstName = selectFirstName(remotePeerUserId, UserRole.PARENT);
                remotePeerLastName = selectLastName(remotePeerUserId, UserRole.PARENT);
                
            } else if (remotePeerUserRole.equals("teacher")) {
                remotePeerUserId = selectUserId(remotePeerUsername, UserRole.TEACHER);
                // get name of peer (teacher)
                remotePeerFirstName = selectFirstName(remotePeerUserId, UserRole.TEACHER);
                remotePeerLastName = selectLastName(remotePeerUserId, UserRole.TEACHER);
                
            } else {
                remotePeerUserId = -1;
                remotePeerLastName = "Administrátor";
                remotePeerFirstName = "";
            }
            
            message.remotePeerId = remotePeerUserId;
            message.remotePeerRole = remotePeerUserRole;
            message.remotePeerFirstName = remotePeerFirstName;
            message.remotePeerLastName = remotePeerLastName;
            
            messages.add(message);
        }
        resultSet.close();
        preparedStatement.close();
        
        return messages;
    }
    
    private static synchronized String selectRoleOfUsername(String username) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_USER_ROLE_OF_USERNAME);
        preparedStatement.setString(1, username);
        ResultSet resultSet = preparedStatement.executeQuery();

        // should return exactly 1 row
        resultSet.next();
        String role = resultSet.getString(1);
        resultSet.close();
        preparedStatement.close();
            
        if (role.equals("A")) {
            return "admin";
        } else if (role.equals("S")) {
            return "student";
        } else if (role.equals("T")) {
            return "teacher";
        } else if (role.equals("P")) {
            return "parent";
        }
        // this should never execute
        return "visitor";
    }
    
    private static synchronized String selectFirstName(int userId, UserRole role) throws SQLException {
        if (role == UserRole.ADMIN) {
            return "";
        }
       
        PreparedStatement preparedStatement;
        if (role == UserRole.STUDENT) {
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_STUDENT_FIRST_NAME);
        } else if (role == UserRole.PARENT) {
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_PARENT_FIRST_NAME);
        } else {
            // TEACHER
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TEACHER_FIRST_NAME);
        }
        preparedStatement.setInt(1, userId);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        String firstName = resultSet.getString(1);
        resultSet.close();
        preparedStatement.close();
        
        return firstName;
    }
    
    private static synchronized String selectLastName(int userId, UserRole role) throws SQLException {
        if (role == UserRole.ADMIN) {
            return "Administrátor";
        }
       
        PreparedStatement preparedStatement;
        if (role == UserRole.STUDENT) {
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_STUDENT_LAST_NAME);
        } else if (role == UserRole.PARENT) {
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_PARENT_LAST_NAME);
        } else {
            // TEACHER
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TEACHER_LAST_NAME);
        }
        preparedStatement.setInt(1, userId);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        String lastName = resultSet.getString(1);
        resultSet.close();
        preparedStatement.close();
        
        return lastName;
    }
    
    public static synchronized boolean userHasUnseenMessages(int userId, UserRole role) throws SQLException {        
        String username = selectUsernameOfUser(role, userId);
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_NEW_MESSAGES_OF_USERNAME);
        preparedStatement.setString(1, username);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        boolean result;
        if (resultSet.next()) {
            result = true;
        } else {
            result = false;
        }
        resultSet.close();
        preparedStatement.close();
        
        return result;
    }
    
    public static synchronized boolean deleteMessageOfUsername(int messageId, String username) throws SQLException {
        boolean deleted = false;
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_UPDATE_MESSAGE_OF_USERNAME_TO_FROM_DELETED);
        preparedStatement.setInt(1, messageId);
        preparedStatement.setString(2, username);
        
        int countUpdated = preparedStatement.executeUpdate();
        preparedStatement.close();
        if (countUpdated == 1) {
            deleted = true;
        }
        
        if (deleted == false) {
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_UPDATE_MESSAGE_OF_USERNAME_TO_TO_DELETED);
            preparedStatement.setInt(1, messageId);
            preparedStatement.setString(2, username);
            
            countUpdated = preparedStatement.executeUpdate();
            preparedStatement.close();
            if (countUpdated == 1) {
                deleted = true;
            }
        }
        
        // remove message completely if deleted by both peers
        if (deleted) {
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_MESSAGE_COMPLETELY_IF_DELETED_BY_BOTH);
            preparedStatement.setInt(1, messageId);
            preparedStatement.executeUpdate();
            preparedStatement.close();
        }
        return deleted;
    }
    
    public static synchronized Map<Integer, contents.entities.NoticeboardNotice> selectNoticesForStudent(int classId) throws SQLException {
        Map<Integer, contents.entities.NoticeboardNotice> notices = new HashMap<Integer, contents.entities.NoticeboardNotice>();
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_NOTICES_FOR_STUDENT);
        preparedStatement.setInt(1, classId);
        ResultSet resultSet = preparedStatement.executeQuery();  
        int i=1;
        while(resultSet.next()){
            String dateString,timeString;
            try {
                String dateAndTime = String.valueOf(resultSet.getTimestamp(5));
                java.text.SimpleDateFormat datetimeFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                java.util.Date date = datetimeFormat.parse(dateAndTime);              
                java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("dd.MM.yyyy");
                dateString = dateFormat.format(date);
                java.text.SimpleDateFormat timeFormat = new java.text.SimpleDateFormat("HH:mm:ss");
                timeString = timeFormat.format(date); 
            } catch (ParseException ex) {
                dateString = "";
                timeString = "";
            }
            String type=resultSet.getString(4);
            if(type.toLowerCase().equals("o")){
                type="notice_others";
            }else if(type.toLowerCase().equals("e")){
                type="notice_exam";
            }else if(type.toLowerCase().equals("a")){
                type="notice_answer";
            }else if(type.toLowerCase().equals("h")){
                type="notice_homework";
            }
            
            int teacherId = resultSet.getInt(2);
            contents.entities.Teacher teacher = Database.getTeacher(teacherId);
            int noticeId = resultSet.getInt(1);
            
            notices.put(i, new contents.entities.NoticeboardNotice(noticeId, teacher,resultSet.getString(3),type,dateString,timeString));
            i++;
        }
        resultSet.close();
        preparedStatement.close();
        return notices;
    }
    
    public static synchronized int selectClassId(int studentId) throws SQLException {
        int classId;
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_CLASS_FOR_STUDENT);
        preparedStatement.setInt(1, studentId);
        ResultSet resultSet = preparedStatement.executeQuery();  
        resultSet.next();
        classId = resultSet.getInt(1);
        resultSet.close();
        preparedStatement.close();
        return classId;
    }   
    
    public static synchronized int selectStudentForParent(int parentId) throws SQLException {
        int studentId;
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_STUDENT_FOR_PARENT);
        preparedStatement.setInt(1, parentId);
        ResultSet resultSet = preparedStatement.executeQuery();  
        resultSet.next();
        studentId = resultSet.getInt(1);
        resultSet.close();
        preparedStatement.close();
        return studentId;
    }
    
    public static synchronized Map<Integer, contents.entities.NoticeboardNotice> selectNoticesForParent(int classId) throws SQLException {
        Map<Integer, contents.entities.NoticeboardNotice> notices = new HashMap<Integer, contents.entities.NoticeboardNotice>();
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_NOTICES_FOR_PARENT);
        preparedStatement.setInt(1, classId);
        ResultSet resultSet = preparedStatement.executeQuery();  
        int i=1;
        while(resultSet.next()){
            String dateString,timeString;
            try {
                String dateAndTime = String.valueOf(resultSet.getTimestamp(5));
                java.text.SimpleDateFormat datetimeFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                java.util.Date date = datetimeFormat.parse(dateAndTime);              
                java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("dd.MM.yyyy");
                dateString = dateFormat.format(date);
                java.text.SimpleDateFormat timeFormat = new java.text.SimpleDateFormat("HH:mm:ss");
                timeString = timeFormat.format(date); 
            } catch (ParseException ex) {
                dateString = "";
                timeString = "";
            }
            String type=resultSet.getString(4);
            if(type.toLowerCase().equals("o")){
                type="notice_others";
            }else if(type.toLowerCase().equals("e")){
                type="notice_exam";
            }else if(type.toLowerCase().equals("a")){
                type="notice_answer";
            }else if(type.toLowerCase().equals("h")){
                type="notice_homework";
            }else if(type.toLowerCase().equals("p")){
                type="notice_parents";
            }
            int teacherId = resultSet.getInt(2);
            contents.entities.Teacher teacher = Database.getTeacher(teacherId);
            int noticeId = resultSet.getInt(1);
            
            notices.put(i, new contents.entities.NoticeboardNotice(noticeId, teacher, resultSet.getString(3),type,dateString,timeString));
            i++;
        }
        resultSet.close();
        preparedStatement.close();
        return notices;
    }
    
    public static synchronized Map<Integer, contents.entities.NoticeboardNotice> selectNoticesForTeacher(int classId) throws SQLException {
        return selectNoticesForParent(classId);
    }
    
    public static synchronized Map<Integer, contents.entities.SubjectGrades> selectGradesForStudent(int studentId) throws SQLException {
        Map<Integer, contents.entities.SubjectGrades> grades = new HashMap<Integer, contents.entities.SubjectGrades>();
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_GRADES_FOR_STUDENT);
        preparedStatement.setInt(1, studentId);
        ResultSet resultSet = preparedStatement.executeQuery();  
        int i=1;
        while(resultSet.next()){
            int gradebookId = resultSet.getInt(1);
            int subjectId = resultSet.getInt(2);
            contents.entities.Subject subject = getSubject(subjectId);
            contents.entities.Student student = Database.getStudent(studentId);
            String gradebook = resultSet.getString(3);
            
            grades.put(i, new contents.entities.SubjectGrades(gradebookId, subject, student, gradebook));
            i++;
        }
        resultSet.close();
        preparedStatement.close();
        return grades;
    }    
    
    public static synchronized void insertNewMessage(String fromUsername, String toUsername, String messageText) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_MESSAGE);
        preparedStatement.setString(1, fromUsername);
        preparedStatement.setString(2, toUsername);
        preparedStatement.setString(4, messageText);
        
        java.util.Date currentDate = new java.util.Date();
        java.text.SimpleDateFormat datetimeFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        preparedStatement.setString(3, datetimeFormat.format(currentDate));
        
        preparedStatement.execute();
        preparedStatement.close();
    }
    
    public static synchronized List<DatabaseEntities.Substitution> selectSubstitutionsOfDate(java.util.Date date) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBSTITUTIONS_OF_DATE);
        
        java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
        String dateString = dateFormat.format(date);
        
        preparedStatement.setString(1, dateString);
        
        List<DatabaseEntities.Substitution> substitutions = new ArrayList<DatabaseEntities.Substitution>();
        DatabaseEntities.Substitution substitution;
        
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            substitution = new DatabaseEntities.Substitution();
            
            substitution.substitutingTeacherId = Integer.valueOf(resultSet.getInt(1));
            if (resultSet.wasNull()) {
                substitution.substitutingTeacherId = null;
            }
            
            substitution.missingTeacherId = resultSet.getInt(2);
            substitution.subjectId = resultSet.getInt(3);
            substitution.classId = resultSet.getInt(4);
            substitution.unit = resultSet.getInt(5);
            
            substitutions.add(substitution);
        }
        
        resultSet.close();
        preparedStatement.close();
        return substitutions;
    }
    
    public static synchronized List<DatabaseEntities.Substitution> selectSubstitutionsOfDateAndClass(java.util.Date date, int classId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBSTITUTIONS_OF_DATE_AND_OF_CLASS);
        
        java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
        String dateString = dateFormat.format(date);
        
        preparedStatement.setString(1, dateString);
        preparedStatement.setInt(2, classId);
        
        List<DatabaseEntities.Substitution> substitutions = new ArrayList<DatabaseEntities.Substitution>();
        DatabaseEntities.Substitution substitution;
        
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            substitution = new DatabaseEntities.Substitution();
            
            substitution.substitutingTeacherId = Integer.valueOf(resultSet.getInt(1));
            if (resultSet.wasNull()) {
                substitution.substitutingTeacherId = null;
            }
            
            substitution.missingTeacherId = resultSet.getInt(2);
            substitution.subjectId = resultSet.getInt(3);
            substitution.classId = classId;
            substitution.unit = resultSet.getInt(4);
            
            substitutions.add(substitution);
        }

        resultSet.close();
        preparedStatement.close();
        return substitutions;
    }
    
    public static synchronized List<DatabaseEntities.Substitution> selectSubstitutionsOfDateAndTeacher(java.util.Date date, int teacherId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBSTITUTIONS_OF_DATE_AND_OF_TEACHER);
        
        java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
        String dateString = dateFormat.format(date);
        
        preparedStatement.setString(1, dateString);
        preparedStatement.setInt(2, teacherId);
        preparedStatement.setInt(3, teacherId);
        
        List<DatabaseEntities.Substitution> substitutions = new ArrayList<DatabaseEntities.Substitution>();
        DatabaseEntities.Substitution substitution;
        
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            substitution = new DatabaseEntities.Substitution();
            
            substitution.substitutingTeacherId = Integer.valueOf(resultSet.getInt(1));
            if (resultSet.wasNull()) {
                substitution.substitutingTeacherId = null;
            }
            
            substitution.missingTeacherId = resultSet.getInt(2);
            substitution.subjectId = resultSet.getInt(3);
            substitution.classId = resultSet.getInt(4);
            substitution.unit = resultSet.getInt(5);
            
            substitutions.add(substitution);
        }

        resultSet.close();
        preparedStatement.close();
        return substitutions;
    }
        
    public static synchronized contents.entities.Subject getSubject(int subjectId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBJECT_NAME_AND_YEARCLASS);
        preparedStatement.setInt(1, subjectId);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        
        String subjectName = resultSet.getString(1);
        Integer yearclass = Integer.valueOf(resultSet.getInt(2));
        
        resultSet.close();
        preparedStatement.close();
        
        return new contents.entities.Subject(subjectId, subjectName, yearclass);
    }
    
    public static synchronized contents.entities.Teacher getTeacher(int teacherId) throws SQLException {       
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TEACHER_NAME_AND_USERNAME);
        preparedStatement.setInt(1, teacherId);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        
        String firstName = resultSet.getString(1);
        String lastName = resultSet.getString(2);
        String username = resultSet.getString(3);
        
        resultSet.close();
        preparedStatement.close();
                
        return new contents.entities.Teacher(teacherId, firstName, lastName, username);
    }
    
    public static synchronized contents.entities.Student getStudent(int studentId) throws SQLException {       
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_STUDENT_NAME_PARENT_ID_AND_CLASS_ID);
        preparedStatement.setInt(1, studentId);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();

        String firstName = resultSet.getString(1);
        String lastName = resultSet.getString(2);
        String username = resultSet.getString(3);
        contents.entities.Parent parent = Database.getParent(resultSet.getInt(4));
        String className = Database.selectClassName(resultSet.getInt(5));
        contents.entities.Class schoolClass = new contents.entities.Class(resultSet.getInt(5), className);
        
        resultSet.close();
        preparedStatement.close();
            
        return new contents.entities.Student(studentId, firstName, lastName, username, parent, schoolClass);
    }
    
    public static synchronized void deleteNotice(int noticeId, int teacherId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_NOTICE);
        preparedStatement.setInt(1, noticeId);
        preparedStatement.setInt(2, teacherId);
        int rowsDeleted = preparedStatement.executeUpdate();
        if (rowsDeleted == 0) {
            throw new SQLException("Teacher tries to remove notice, which he does not own.");
        }
        preparedStatement.close();
    }
    
    public static synchronized void addNotice(int teacherId, int classId, String text, String type) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_NOTICE);
        
        java.util.Date currentDate = new java.util.Date();
        java.text.SimpleDateFormat datetimeFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        preparedStatement.setInt(1, teacherId);
        preparedStatement.setInt(2, classId);
        preparedStatement.setString(3, text);
        preparedStatement.setString(4, type);
        preparedStatement.setString(5, datetimeFormat.format(currentDate));
        
        preparedStatement.execute();
        preparedStatement.close();
    }
    
    public static synchronized List<DatabaseEntities.Tutorage> selectAllTutorages() throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(DatabaseQueries.QUERY_SELECT_ALL_TUTORAGES);
        
        List<DatabaseEntities.Tutorage> tutorages = parseTutoragesFromResultSet(resultSet);

        resultSet.close();
        statement.close();
        return tutorages;
    }
    
    public static synchronized List<DatabaseEntities.Tutorage> selectTutoragesOfStudent(int studentId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TUTORAGES_OF_STUDENT);
        preparedStatement.setInt(1, studentId);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        List<DatabaseEntities.Tutorage> tutorages = parseTutoragesFromResultSet(resultSet);

        resultSet.close();
        preparedStatement.close();
        return tutorages;
    }
    
    public static synchronized List<DatabaseEntities.Tutorage> selectRemoteTutoragesForSubjectWithoutThoseOfStudent(int subjectId, int studentId, String tutorageType) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_REMOTE_TUTORAGES_FOR_SUBJECT_WITHOUT_THOSE_OF_STUDENT);
        preparedStatement.setInt(1, subjectId);
        preparedStatement.setInt(2, studentId);
        preparedStatement.setString(3, tutorageType);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        List<DatabaseEntities.Tutorage> tutorages = parseTutoragesFromResultSet(resultSet);

        resultSet.close();
        preparedStatement.close();
        return tutorages;
    }
    
    private static synchronized List<DatabaseEntities.Tutorage> parseTutoragesFromResultSet(ResultSet resultSet) throws SQLException {
        List<DatabaseEntities.Tutorage> tutorages = new ArrayList<DatabaseEntities.Tutorage>();
        DatabaseEntities.Tutorage tutorage;
        while (resultSet.next()) {
            tutorage = new DatabaseEntities.Tutorage();
            tutorage.tutorageId = resultSet.getInt(1);
            tutorage.subjectId = resultSet.getInt(2);
            tutorage.studentId = resultSet.getInt(3);
            tutorage.type = resultSet.getString(4);
            tutorage.className = resultSet.getString(6);
            
            String dateString = resultSet.getString(5);
            
            try {
                java.text.SimpleDateFormat datetimeFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                java.util.Date date = datetimeFormat.parse(dateString);
                
                java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("dd.MM.yyyy");
                tutorage.dateString = dateFormat.format(date);
                
                java.text.SimpleDateFormat timeFormat = new java.text.SimpleDateFormat("HH:mm:ss");
                tutorage.timeString = timeFormat.format(date);
                
            } catch (ParseException exception) {
                // thiw never happens
                throw new SQLException(exception.getMessage());
            }
            
            tutorages.add(tutorage);
        }
        return tutorages;
    }
    
    public static synchronized void deleteTutorage(int tutorageId, int studentId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_TUTORAGE);
        preparedStatement.setInt(1, tutorageId);
        preparedStatement.setInt(2, studentId);
        int rowsDeleted = preparedStatement.executeUpdate();
        if (rowsDeleted == 0) {
            throw new SQLException("Teacher tries to remove notice, which he does not own.");
        }
        preparedStatement.close();
    }
    
    public static synchronized void addTutorage(int studentId, int subjectId, String tutorageType) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_TUTORAGE);
        
        java.util.Date currentDate = new java.util.Date();
        java.text.SimpleDateFormat datetimeFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        preparedStatement.setInt(1, studentId);
        preparedStatement.setInt(2, subjectId);
        preparedStatement.setString(3, tutorageType);
        preparedStatement.setString(4, datetimeFormat.format(currentDate));
        
        preparedStatement.execute();
        preparedStatement.close();
    }
    
    public static synchronized List<contents.entities.ClassesSubjectsOfTeacher> selectClassesSubjectsOfTeacher(int teacherId) throws SQLException {        
        Map<Integer, List<contents.entities.Subject> > classesSubjectsMap = new HashMap<Integer, List<contents.entities.Subject> >();
                
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_CLASSES_AND_SUBJECTS_OF_TEACHER);
        preparedStatement.setInt(1, teacherId);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int classId = resultSet.getInt(1);
            contents.entities.Subject subject = Database.getSubject(resultSet.getInt(2));
            
            if (!classesSubjectsMap.containsKey(Integer.valueOf(classId))) {
                classesSubjectsMap.put(Integer.valueOf(classId), new ArrayList<contents.entities.Subject>());
            }
            classesSubjectsMap.get(Integer.valueOf(classId)).add(subject);
        }
        resultSet.close();
        preparedStatement.close();
        
        List<contents.entities.ClassesSubjectsOfTeacher> classesSubjects = new ArrayList<contents.entities.ClassesSubjectsOfTeacher>();
        
        for (Map.Entry<Integer, List<contents.entities.Subject> > entry : classesSubjectsMap.entrySet()) {
            Collections.sort(entry.getValue(), new contents.entities.Subject.ByNameAndYearClassComparator());
            
            String className = Database.selectClassName(entry.getKey().intValue());
            contents.entities.Class schoolClass = new contents.entities.Class(entry.getKey().intValue(), className);
            classesSubjects.add(new contents.entities.ClassesSubjectsOfTeacher(schoolClass, entry.getValue()));
        }
        
        Collections.sort(classesSubjects, new contents.entities.ClassesSubjectsOfTeacher.ByClassComparator());
        
        return classesSubjects;
    }
    
    public static synchronized boolean teacherTeachesClassSubject(int teacherId, int classId, int subjectId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TIMETABLE_OF_TEACHER_CLASS_AND_SUBJECT);
        preparedStatement.setInt(1, teacherId);
        preparedStatement.setInt(2, classId);
        preparedStatement.setInt(3, subjectId);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            resultSet.close();
            preparedStatement.close();
            return false;
        }
        
        resultSet.close();
        preparedStatement.close();
        return true;
    }
    
    public static synchronized Map<Integer, contents.entities.SubjectGrades> selectGradesOfClassAndSubject(int classId, int subjectId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_GRADES_OF_CLASS_AND_SUBJECT);
        preparedStatement.setInt(1, subjectId);
        preparedStatement.setInt(2, classId);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        List<contents.entities.SubjectGrades> subjectGrades = new ArrayList<contents.entities.SubjectGrades>();
        
        while (resultSet.next()) {
            int gradebookId = resultSet.getInt(1);
            contents.entities.Subject subject = Database.getSubject(subjectId);
            contents.entities.Student student = Database.getStudent(resultSet.getInt(2));
            String grades = resultSet.getString(3);
            subjectGrades.add(new contents.entities.SubjectGrades(gradebookId, subject, student, grades));
        }
        Collections.sort(subjectGrades, new contents.entities.SubjectGrades.ByStudentNameComparator());
        
        Map<Integer, contents.entities.SubjectGrades> subjectGradesMap = new HashMap<Integer, contents.entities.SubjectGrades>();
        for (int i = 0; i < subjectGrades.size(); i++) {
            subjectGradesMap.put(i+1, subjectGrades.get(i));
        }
        resultSet.close();
        preparedStatement.close();
        
        return subjectGradesMap;
    }
    
    public static synchronized void setGrades(int classId, int subjectId, List<contents.entities.SubjectGrades> subjectGrades) throws SQLException {
        for (contents.entities.SubjectGrades subjectGrade : subjectGrades) {
            PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_GRADES_OF_CLASS_AND_SUBJECT_WITH_ID);
            preparedStatement.setInt(1, subjectId);
            preparedStatement.setInt(2, classId);
            preparedStatement.setInt(3, subjectGrade.getGradebookId());
            ResultSet resultSet = preparedStatement.executeQuery();
            // if returns 0, will fail as was minded
            if (!resultSet.next()) {
                resultSet.close();
                preparedStatement.close();
                throw new SQLException("Failed to set grades.");
            }
            resultSet.close();
            preparedStatement.close();
            
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_UPDATE_GRADES_OF_GRADEBOOK);
            preparedStatement.setString(1, subjectGrade.getGrades());
            preparedStatement.setInt(2, subjectGrade.getGradebookId());
            preparedStatement.executeUpdate();
            preparedStatement.close();
        }
    }
    
    public static synchronized Map<Integer, contents.entities.Absence> selectAbsencesOfTeacherSinceDate(int teacherId, java.util.Date date) throws SQLException {
        Map<Integer, contents.entities.Absence> absences = new HashMap<Integer, contents.entities.Absence>();
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_ABSENCES_FOR_TEACHER_SINCE_DATE);
        preparedStatement.setInt(1, teacherId);
        java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
        String dateString = dateFormat.format(date);
        preparedStatement.setString(2, dateString);
        ResultSet resultSet = preparedStatement.executeQuery();  
        
        int i = 1;
        while (resultSet.next()) {
            int absenceId = resultSet.getInt(1);
            
            try {
                dateString = String.valueOf(resultSet.getTimestamp(2));
                date = dateFormat.parse(dateString);
            } catch (ParseException exception) {
                // this never happens
                date = new java.util.Date();
            }
            
            int fromUnit = resultSet.getInt(3);
            int toUnit = resultSet.getInt(4);
            
            contents.entities.Teacher teacher = Database.getTeacher(teacherId);
            
            absences.put(i, new contents.entities.Absence(absenceId, teacher, date, fromUnit, toUnit));
            i++;
        }
        resultSet.close();
        preparedStatement.close();
        return absences;
    }
    
    public static synchronized Map<Integer, contents.entities.Absence> selectAllAbsencesSinceDate(java.util.Date date) throws SQLException {
        Map<Integer, contents.entities.Absence> absences = new HashMap<Integer, contents.entities.Absence>();
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_ALL_ABSENCES_SINCE_DATE);
        java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
        String dateString = dateFormat.format(date);
        preparedStatement.setString(1, dateString);
        ResultSet resultSet = preparedStatement.executeQuery();  
        
        int i = 1;
        while (resultSet.next()) {
            int absenceId = resultSet.getInt(1);            
            int teacherId = resultSet.getInt(2);
            contents.entities.Teacher teacher = Database.getTeacher(teacherId);
            
            try {
                dateString = String.valueOf(resultSet.getTimestamp(3));
                date = dateFormat.parse(dateString);
            } catch (ParseException exception) {
                // this never happens
                date = new java.util.Date();
            }
            
            int fromUnit = resultSet.getInt(4);
            int toUnit = resultSet.getInt(5);
            
            absences.put(i, new contents.entities.Absence(absenceId, teacher, date, fromUnit, toUnit));
            i++;
        }
        resultSet.close();
        preparedStatement.close();
        return absences;
    }
    
    public static synchronized void addAbsence(int teacherId, java.util.Date date, int fromUnit, int toUnit) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_ABSENCE);
        
        java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
        
        preparedStatement.setInt(1, teacherId);
        preparedStatement.setString(2, dateFormat.format(date));
        preparedStatement.setInt(3, fromUnit);
        preparedStatement.setInt(4, toUnit);
        
        preparedStatement.execute();
        preparedStatement.close();
        
        // remove substitutions of the day and time where teacher is substitutor
        int dayOfWeek = date.getDay();
        if (dayOfWeek < 1 || dayOfWeek > 5) {
            return;
        }
        
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBSTITUTION_IDS_WHERE_TEACHER_IS_SUBSTITUTOR);
        preparedStatement.setInt(1, dayOfWeek);
        preparedStatement.setInt(2, fromUnit);
        preparedStatement.setInt(3, toUnit);
        preparedStatement.setString(4, dateFormat.format(date));
        preparedStatement.setInt(5, teacherId);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        List<Integer> substitutionIds = new ArrayList<Integer>();
        while (resultSet.next()) {
            substitutionIds.add(resultSet.getInt(1));
        }
        resultSet.close();
        preparedStatement.close();
        
        for (int i = 0; i < substitutionIds.size(); i++) {
            Database.deleteSubstitution(substitutionIds.get(i).intValue());
        }
    }
    
    public static synchronized void deleteAbsence(int absenceId, int teacherId) throws SQLException {
        // should remove substitutions cascadely, if are any
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_ABSENCE_OF_TEACHER);
        preparedStatement.setInt(1, absenceId);
        preparedStatement.setInt(2, teacherId);
        int rowsDeleted = preparedStatement.executeUpdate();
        if (rowsDeleted == 0) {
            throw new SQLException("Teacher tries to remove absence, which does not belong to him.");
        }
        preparedStatement.close();
    }
    
    public static synchronized void deleteAbsence(int absenceId) throws SQLException {
        // should remove substitutions cascadely, if are any
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_ABSENCE);
        preparedStatement.setInt(1, absenceId);
        int rowsDeleted = preparedStatement.executeUpdate();
        if (rowsDeleted == 0) {
            throw new SQLException("deleteAbsence: Absence with ID " + absenceId + " does not exist.");
        }
        preparedStatement.close();
    }
    
    public static synchronized boolean addSubject(String subjectName, int yearClass) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBJECT_ID);
        preparedStatement.setString(1, subjectName);
        preparedStatement.setInt(2, yearClass);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        if (resultSet.next()) {
            resultSet.close();
            preparedStatement.close();
            return false;
        }
        resultSet.close();
        preparedStatement.close();
        
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_SUBJECT);
        preparedStatement.setString(1, subjectName);
        preparedStatement.setInt(2, yearClass);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        return true;
    }
    
    public static synchronized boolean editSubject(int subjectId, String subjectName, int yearClass) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBJECT_ID);
        preparedStatement.setString(1, subjectName);
        preparedStatement.setInt(2, yearClass);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        if (resultSet.next()) {
            if (resultSet.getInt(1) != subjectId || resultSet.next()) {
                resultSet.close();
                preparedStatement.close();
                return false;
            }
        }
        resultSet.close();
        preparedStatement.close();
        
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_UPDATE_SUBJECT);
        preparedStatement.setString(1, subjectName);
        preparedStatement.setInt(2, yearClass);
        preparedStatement.setInt(3, subjectId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        return true;
    }
    
    public static synchronized void deleteSubject(int subjectId) throws SQLException {
        // should remove cascadely: tutorages, gradebooks, classes_subjects, timetables, teachers_subjects
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_SUBJECT);
        preparedStatement.setInt(1, subjectId);
        int rowsDeleted = preparedStatement.executeUpdate();
        if (rowsDeleted == 0) {
            throw new SQLException("deleteSubject: Subject with ID " + subjectId + " does not exist.");
        }
        preparedStatement.close();
    }
    
    public static synchronized boolean addClass(String className) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_CLASS_ID);
        preparedStatement.setString(1, className);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        if (resultSet.next()) {
            resultSet.close();
            preparedStatement.close();
            return false;
        }
        resultSet.close();
        preparedStatement.close();
        
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_CLASS);
        preparedStatement.setString(1, className);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        return true;
    }
    
    public static synchronized boolean editClass(int classId, String className, List<Integer> newSubjectIds) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_CLASS_ID);
        preparedStatement.setString(1, className);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        if (resultSet.next()) {
            if (resultSet.getInt(1) != classId || resultSet.next()) {
                resultSet.close();
                preparedStatement.close();
                return false;
            }
        }
        resultSet.close();
        preparedStatement.close();
        
        // update class name
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_UPDATE_CLASS_NAME);
        preparedStatement.setString(1, className);
        preparedStatement.setInt(2, classId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        
        List<Integer> currentSubjectIds = Database.selectSubjectIdsOfClass(classId);
        
        // add new subjects
        List<Integer> subjectIdsToAdd = new ArrayList<Integer>();
        for (int i = 0; i < newSubjectIds.size(); i++) {
            if (!currentSubjectIds.contains(newSubjectIds.get(i))) {
                subjectIdsToAdd.add(newSubjectIds.get(i));
            }
        }
        for (int i = 0; i < subjectIdsToAdd.size(); i++) {
            addSubjectIdToClass(classId, subjectIdsToAdd.get(i).intValue());
        }
        
        // remove subjects
        List<Integer> subjectIdsToRemove = new ArrayList<Integer>();
        for (int i = 0; i < currentSubjectIds.size(); i++) {
            if (!newSubjectIds.contains(currentSubjectIds.get(i))) {
                subjectIdsToRemove.add(currentSubjectIds.get(i));
            }
        }
        for (int i = 0; i < subjectIdsToRemove.size(); i++) {
            removeSubjectIdFromClass(classId, subjectIdsToRemove.get(i).intValue());
        }

        return true;
    }
    
    private static synchronized void addSubjectIdToClass(int classId, int subjectId) throws SQLException {
        // add row to table classes_subjects
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_CLASSES_SUBJECTS);
        preparedStatement.setInt(1, classId);
        preparedStatement.setInt(2, subjectId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        
        // add gradebooks for students of class for new subject
        Map<Integer, contents.entities.Student> studentsOfClass = Database.selectStudentsOfClass(classId);        
        for (Map.Entry<Integer, contents.entities.Student> student : studentsOfClass.entrySet()) {
            addGradebookForSubjectForStudent(subjectId, student.getValue().getStudentId());
        }
    }
    
    private static synchronized void addGradebookForSubjectForStudent(int subjectId, int studentId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_GRADEBOOK);
        preparedStatement.setInt(1, subjectId);
        preparedStatement.setInt(2, studentId);
        preparedStatement.setString(3, "");
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    
    private static synchronized void removeGradebookForSubjectFromStudent(int subjectId, int studentId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_GRADEBOOK_OF_STUDENT_FOR_SUBJECT);
        preparedStatement.setInt(1, studentId);
        preparedStatement.setInt(2, subjectId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    
    private static synchronized void removeSubjectIdFromClass(int classId, int subjectId) throws SQLException {
        // remove row from table classes_subjects
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_CLASSES_SUBJECTS);
        preparedStatement.setInt(1, classId);
        preparedStatement.setInt(2, subjectId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        
        // remove timetables (and substitutions cascadely)
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_TIMETABLES_OF_CLASS);
        preparedStatement.setInt(1, classId);
        preparedStatement.setInt(2, subjectId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        
        // remove gradebooks from students of class for removed subject
        Map<Integer, contents.entities.Student> studentsOfClass = Database.selectStudentsOfClass(classId);        
        for (Map.Entry<Integer, contents.entities.Student> student : studentsOfClass.entrySet()) {
            removeGradebookForSubjectFromStudent(subjectId, student.getValue().getStudentId());
        }
    }
    
    public static synchronized boolean deleteClass(int classId) throws SQLException {
        Map<Integer, contents.entities.Student> students = Database.selectStudentsOfClass(classId);
        if (!students.isEmpty()) {
            return false;
        }
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_CLASS);
        preparedStatement.setInt(1, classId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        return true;
    }
    
    public static synchronized List<Integer> selectSubjectIdsOfClass(int classId) throws SQLException {
        List<Integer> subjects = new ArrayList<Integer>();
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBJECT_IDS_OF_CLASS);
        preparedStatement.setInt(1, classId);
        ResultSet resultSet = preparedStatement.executeQuery(); 
        while (resultSet.next()) {
            subjects.add(Integer.valueOf(resultSet.getInt(1)));
        }
        resultSet.close();
        preparedStatement.close();
        return subjects;
    }
    
    public static synchronized int addTeacher(String firstName, String lastName, String username, String passwordHash, List<Integer> subjectIds) throws SQLException {       
        insertAccount(username, passwordHash, UserRole.TEACHER);
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_TEACHER);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, firstName);
        preparedStatement.setString(3, lastName);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        
        int teacherId = Database.selectUserId(username, UserRole.TEACHER);
        
        for (int i = 0; i < subjectIds.size(); i++) {
            addSubjectIdToTeacher(teacherId, subjectIds.get(i).intValue());
        }
        
        return teacherId;
    }
    
    private static synchronized void addSubjectIdToTeacher(int teacherId, int subjectId) throws SQLException {
        // add row to table classes_subjects
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_TEACHERS_SUBJECTS);
        preparedStatement.setInt(1, teacherId);
        preparedStatement.setInt(2, subjectId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    
    public static synchronized void editTeacher(int teacherId, String firstName, String lastName, List<Integer> newSubjectIds) throws SQLException {
        // update teacher name
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_UPDATE_TEACHER_NAME);
        preparedStatement.setString(1, firstName);
        preparedStatement.setString(2, lastName);
        preparedStatement.setInt(3, teacherId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        
        List<Integer> currentSubjectIds = Database.selectSubjectIdsOfTeacher(teacherId);
        
        // add new subjects
        List<Integer> subjectIdsToAdd = new ArrayList<Integer>();
        for (int i = 0; i < newSubjectIds.size(); i++) {
            if (!currentSubjectIds.contains(newSubjectIds.get(i))) {
                subjectIdsToAdd.add(newSubjectIds.get(i));
            }
        }
        for (int i = 0; i < subjectIdsToAdd.size(); i++) {
            addSubjectIdToTeacher(teacherId, subjectIdsToAdd.get(i).intValue());
        }
        
        // remove subjects
        List<Integer> subjectIdsToRemove = new ArrayList<Integer>();
        for (int i = 0; i < currentSubjectIds.size(); i++) {
            if (!newSubjectIds.contains(currentSubjectIds.get(i))) {
                subjectIdsToRemove.add(currentSubjectIds.get(i));
            }
        }
        for (int i = 0; i < subjectIdsToRemove.size(); i++) {
            removeSubjectIdFromTeacher(teacherId, subjectIdsToRemove.get(i).intValue());
        }
    }
    
    private static synchronized void removeSubjectIdFromTeacher(int teacherId, int subjectId) throws SQLException {
        // remove row from table classes_subjects
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_TEACHERS_SUBJECTS);
        preparedStatement.setInt(1, teacherId);
        preparedStatement.setInt(2, subjectId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        
        // remove timetables (and substitutions cascadely)
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_TIMETABLES_OF_TEACHER);
        preparedStatement.setInt(1, teacherId);
        preparedStatement.setInt(2, subjectId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    
    public static synchronized void setNewPasswordHashToUser(int userId, UserRole userRole, String newPasswordHash) throws SQLException {
        String username = Database.selectUsernameOfUser(userRole, userId);
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_UPDATE_PASSWORD_OF_USERNAME);
        preparedStatement.setString(1, newPasswordHash);
        preparedStatement.setString(2, username);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    
    public static synchronized void deleteTeacher(int teacherId) throws SQLException {
        String username = Database.selectUsernameOfUser(UserRole.TEACHER, teacherId);
        
        // should remove cascadely: messages, teacher and then notices, absences, substitutions, timetables, teachers_subjects
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_USER);
        preparedStatement.setString(1, username);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    
    public static synchronized boolean usernameExists(String username) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_USERNAME);
        preparedStatement.setString(1, username);
        ResultSet resultSet = preparedStatement.executeQuery();
        boolean result;
        if (resultSet.next()) {
            result = true;
        } else {
            result =  false;
        }
        resultSet.close();
        preparedStatement.close();
        return result;
    }
    
    public static synchronized List<Integer> selectSubjectIdsOfTeacher(int teacherId) throws SQLException {
        List<Integer> subjects = new ArrayList<Integer>();
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBJECT_IDS_OF_TEACHER);
        preparedStatement.setInt(1, teacherId);
        ResultSet resultSet = preparedStatement.executeQuery(); 
        while (resultSet.next()) {
            subjects.add(Integer.valueOf(resultSet.getInt(1)));
        }
        resultSet.close();
        preparedStatement.close();
        return subjects;
    }
    
    private static synchronized void insertAccount(String username, String passwordHash, UserRole role) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_ACCOUNT);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, passwordHash);
        
        switch (role) {
            case STUDENT:
                preparedStatement.setString(3, "S");
                break;
            case PARENT:
                preparedStatement.setString(3, "P");
                break;
            case TEACHER:
                preparedStatement.setString(3, "T");
                break;
            default:
                //ADMIN
                preparedStatement.setString(3, "A");
                break;
        }
        
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    
    public static synchronized int addStudent(String studentFirstName, String studentLastName, String studentUsername, String studentPasswordHash,
            String parentFirstName, String parentLastName, String parentUsername, String parentPasswordHash, int classId) throws SQLException {
        
        // first add parent
        insertAccount(parentUsername, parentPasswordHash, UserRole.PARENT);
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_PARENT);
        preparedStatement.setString(1, parentUsername);
        preparedStatement.setString(2, parentFirstName);
        preparedStatement.setString(3, parentLastName);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        
        int parentId = Database.selectUserId(parentUsername, UserRole.PARENT);
        
        // now add student
        insertAccount(studentUsername, studentPasswordHash, UserRole.STUDENT);
        
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_STUDENT);
        preparedStatement.setString(1, studentUsername);
        preparedStatement.setString(2, studentFirstName);
        preparedStatement.setString(3, studentLastName);
        preparedStatement.setInt(4, parentId);
        preparedStatement.setInt(5, classId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        
        int studentId = Database.selectUserId(studentUsername, UserRole.STUDENT);
        
        // add gradebook for all subjects of class
        List<Integer> subjectIds = Database.selectSubjectIdsOfClass(classId);
        
        for (int i = 0; i < subjectIds.size(); i++) {
            addGradebookForSubjectForStudent(subjectIds.get(i).intValue(), studentId);
        }
        
        return studentId;
    }
    
    public static synchronized void editStudent(int studentId, String studentFirstName, String studentLastName, 
            String parentFirstName, String parentLastName, int classId) throws SQLException {
        
        contents.entities.Student student = Database.getStudent(studentId);
        int parentId = student.getParent().getParentId();
        List<Integer> currentSubjectIds = Database.selectSubjectIdsOfClass(student.getSchoolClass().getClassId());
        
        // update parent name
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_UPDATE_PARENT_NAME);
        preparedStatement.setString(1, parentFirstName);
        preparedStatement.setString(2, parentLastName);
        preparedStatement.setInt(3, parentId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        
        // update student name
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_UPDATE_STUDENT_NAME_AND_CLASS_ID);
        preparedStatement.setString(1, studentFirstName);
        preparedStatement.setString(2, studentLastName);
        preparedStatement.setInt(3, classId);
        preparedStatement.setInt(4, studentId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
                
        List<Integer> newSubjectIds = Database.selectSubjectIdsOfClass(classId);
        
        // add new subjects
        List<Integer> subjectIdsToAdd = new ArrayList<Integer>();
        for (int i = 0; i < newSubjectIds.size(); i++) {
            if (!currentSubjectIds.contains(newSubjectIds.get(i))) {
                subjectIdsToAdd.add(newSubjectIds.get(i));
            }
        }
        for (int i = 0; i < subjectIdsToAdd.size(); i++) {
            addGradebookForSubjectForStudent(subjectIdsToAdd.get(i).intValue(), studentId);
        }
        
        // remove subjects
        List<Integer> subjectIdsToRemove = new ArrayList<Integer>();
        for (int i = 0; i < currentSubjectIds.size(); i++) {
            if (!newSubjectIds.contains(currentSubjectIds.get(i))) {
                subjectIdsToRemove.add(currentSubjectIds.get(i));
            }
        }
        for (int i = 0; i < subjectIdsToRemove.size(); i++) {
            removeGradebookForSubjectFromStudent(subjectIdsToRemove.get(i).intValue(), studentId);
        }
    }
    
    public static synchronized void deleteStudent(int studentId) throws SQLException {
        contents.entities.Student student = Database.getStudent(studentId);
        String studentUsername = student.getUsername();
        String parentUsername = student.getParent().getUsername();
        
        // remove student: should remove cascadely: messages, student and then gradebook, tutorages
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_USER);
        preparedStatement.setString(1, studentUsername);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        
        // remove parent: should remove cascadely: messages, parent
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_USER);
        preparedStatement.setString(1, parentUsername);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    
    public static synchronized void deleteTimetableOfClass(int classId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_ALL_TIMETABLES_OF_CLASS);
        preparedStatement.setInt(1, classId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    
    public static synchronized boolean isValidTimetable(int classId, database.DatabaseEntities.Timetable timetable) throws SQLException {
        // check that subject of timetable exist
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBJECT_NAME_AND_YEARCLASS);
        preparedStatement.setInt(1, timetable.subjectId);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            resultSet.close();
            preparedStatement.close();
            return false;
        }
        resultSet.close();
        preparedStatement.close();
        
        // check that teacher of timetable exist
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TEACHER_FIRST_NAME);
        preparedStatement.setInt(1, timetable.teacherId);
        resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            resultSet.close();
            preparedStatement.close();
            return false;
        }
        resultSet.close();
        preparedStatement.close();
        
        // check that teacher teaches subject of timetable
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_EXISTENCE_OF_TEACHERS_SUBJECTS_WITH_TEACHER_ID_AND_SUBJECT_ID);
        preparedStatement.setInt(1, timetable.teacherId);
        preparedStatement.setInt(2, timetable.subjectId);
        resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            resultSet.close();
            preparedStatement.close();
            return false;
        }
        resultSet.close();
        preparedStatement.close();
        
        // check that class has subject of timetable
        preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_EXISTENCE_OF_CLASSES_SUBJECTS_WITH_CLASS_ID_AND_SUBJECT_ID);
        preparedStatement.setInt(1, classId);
        preparedStatement.setInt(2, timetable.subjectId);
        resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            resultSet.close();
            preparedStatement.close();
            return false;
        }
        resultSet.close();
        preparedStatement.close();
        
        return true;
    }
    
    public static synchronized boolean timetableIsNotInCollision(database.DatabaseEntities.Timetable timetable) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TIMETABLE_ID_WHERE_TEACHER_DAY_AND_UNIT);
        preparedStatement.setInt(1, timetable.teacherId);
        preparedStatement.setInt(2, timetable.day);
        preparedStatement.setInt(3, timetable.unit);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            resultSet.close();
            preparedStatement.close();
            return false;
        }
        resultSet.close();
        preparedStatement.close();
        
        return true;
    }
    
    public static synchronized void insertTimetablesForClass(int classId, List<database.DatabaseEntities.Timetable> timetables) throws SQLException {
        PreparedStatement preparedStatement;
        for (int i = 0; i < timetables.size(); i++) {
            preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_TIMETABLE);
            preparedStatement.setInt(1, classId);
            preparedStatement.setInt(2, timetables.get(i).subjectId);
            preparedStatement.setInt(3, timetables.get(i).teacherId);
            preparedStatement.setInt(4, timetables.get(i).day);
            preparedStatement.setInt(5, timetables.get(i).unit);
            preparedStatement.executeUpdate();
            preparedStatement.close();
        }
    }
    
    public static synchronized List<contents.entities.Timetable> selectTimetablesOfClass(int classId) throws SQLException {
        List<contents.entities.Timetable> timetables = new ArrayList<contents.entities.Timetable>();
        
        // timetables will be sorted by day and time automatically
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TIMETABLES_OF_CLASS);
        preparedStatement.setInt(1, classId);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        while (resultSet.next()) {
            int timetableId = resultSet.getInt(1);
            contents.entities.Subject subject = Database.getSubject(resultSet.getInt(2));
            contents.entities.Teacher teacher = Database.getTeacher(resultSet.getInt(3));
            String className = Database.selectClassName(classId);
            contents.entities.Class schoolClass = new contents.entities.Class(classId, className);
            int day = resultSet.getInt(4);
            int unit = resultSet.getInt(5);
            
            timetables.add(new contents.entities.Timetable(timetableId, subject, teacher, schoolClass, day, unit));
        }
        resultSet.close();
        preparedStatement.close();
        
        return timetables;
    }
    
    public static synchronized List<contents.entities.Timetable> selectTimetablesOfTeacher(int teacherId) throws SQLException {
        List<contents.entities.Timetable> timetables = new ArrayList<contents.entities.Timetable>();
        
        // timetables will be sorted by day and time automatically
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TIMETABLES_OF_TEACHER);
        preparedStatement.setInt(1, teacherId);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        while (resultSet.next()) {
            int timetableId = resultSet.getInt(1);
            contents.entities.Subject subject = Database.getSubject(resultSet.getInt(2));
            contents.entities.Teacher teacher = Database.getTeacher(teacherId);
            int classId = resultSet.getInt(3);
            String className = Database.selectClassName(classId);
            contents.entities.Class schoolClass = new contents.entities.Class(classId, className);
            int day = resultSet.getInt(4);
            int unit = resultSet.getInt(5);
            
            timetables.add(new contents.entities.Timetable(timetableId, subject, teacher, schoolClass, day, unit));
        }
        resultSet.close();
        preparedStatement.close();
        
        return timetables;
    }
    
    public static synchronized List<DatabaseEntities.Substitution> selectSubstitutionsSinceDate(java.util.Date date) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBSTITUTIONS_SINCE_DATE);
        
        java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
        String dateString = dateFormat.format(date);
        
        preparedStatement.setString(1, dateString);
        
        List<DatabaseEntities.Substitution> substitutions = new ArrayList<DatabaseEntities.Substitution>();
        DatabaseEntities.Substitution substitution;
        
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            substitution = new DatabaseEntities.Substitution();
            
            substitution.substitutingTeacherId = Integer.valueOf(resultSet.getInt(1));
            if (resultSet.wasNull()) {
                substitution.substitutingTeacherId = null;
            }
            
            substitution.missingTeacherId = resultSet.getInt(2);
            substitution.subjectId = resultSet.getInt(3);
            substitution.classId = resultSet.getInt(4);
            substitution.unit = resultSet.getInt(5);
            substitution.dateString = resultSet.getString(6);
            substitution.substitutionId = resultSet.getInt(7);
            
            substitutions.add(substitution);
        }
        resultSet.close();
        preparedStatement.close();
        
        return substitutions;
    }
    
    public static synchronized contents.entities.Timetable getTimetable(int timetableId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TIMETABLE);
        preparedStatement.setInt(1, timetableId);
        ResultSet resultSet = preparedStatement.executeQuery();
        
        resultSet.next();
        contents.entities.Subject subject = Database.getSubject(resultSet.getInt(1));
        contents.entities.Teacher teacher = Database.getTeacher(resultSet.getInt(2));
        int classId = resultSet.getInt(3);
        String className = Database.selectClassName(classId);
        contents.entities.Class schoolClass = new contents.entities.Class(classId, className);
        int day = resultSet.getInt(4);
        int unit = resultSet.getInt(5);
        
        resultSet.close();
        preparedStatement.close();
            
        return new contents.entities.Timetable(timetableId, subject, teacher, schoolClass, day, unit);
    }
    
    public static synchronized contents.entities.Absence getAbsence(int absenceId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_ABSENCE);
        preparedStatement.setInt(1, absenceId);
        ResultSet resultSet = preparedStatement.executeQuery();  
        
        resultSet.next();
        int teacherId = resultSet.getInt(1);
        contents.entities.Teacher teacher = Database.getTeacher(teacherId);
        
        java.util.Date date;
        try {
            String dateString = String.valueOf(resultSet.getTimestamp(2));
            java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
            date = dateFormat.parse(dateString);
        } catch (ParseException exception) {
                // this never happens
                date = new java.util.Date();
        }
            
        int fromUnit = resultSet.getInt(3);
        int toUnit = resultSet.getInt(4);
        
        resultSet.close();
        preparedStatement.close();
            
        return new contents.entities.Absence(absenceId, teacher, date, fromUnit, toUnit);
    }
    
    public static synchronized void deleteSubstitution(int substitutionId) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_REMOVE_SUBSTITUTION);
        preparedStatement.setInt(1, substitutionId);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    
    public static synchronized Map<Integer, contents.entities.Teacher> selectUnoccupiedTeachersForSubstitution(int timetableId, int absenceId) throws SQLException {
        contents.entities.Timetable timetable = Database.getTimetable(timetableId);
        contents.entities.Absence absence = Database.getAbsence(absenceId);
        
        int teacherId = timetable.getTeacher().getTeacherId();
        
        // get all teachers
        Map<Integer, contents.entities.Teacher> allTeachers = Database.selectTeachers();
        
        // get teachers who are in timetable on same day and in same time
        Map<Integer, contents.entities.Teacher> teachingTeachers = Database.selectTeachingTeachers(timetable.getDay(), timetable.getTime());
        
        // get teachers who are absenting on same date and in same time
        Map<Integer, contents.entities.Teacher> missingTeachers = Database.selectMissingTeachers(absence.getDate(), timetable.getTime());
        
        // get teachers who are substituting on same day and in same time
        Map<Integer, contents.entities.Teacher> substitutingTeachers = Database.selectSubstitutingTeachers(absence.getDate(), timetable.getTime());
        
        
        Map<Integer, contents.entities.Teacher> unoccupiedTeachers = new HashMap<Integer, contents.entities.Teacher>();
        int i = 1;
        for (Map.Entry<Integer, contents.entities.Teacher> teacher : allTeachers.entrySet()) {
            if (teacher.getValue().getTeacherId() == teacherId) {
                continue;
            }
            if (teachingTeachers.containsValue(teacher.getValue())) {
                continue;
            }
            if (missingTeachers.containsValue(teacher.getValue())) {
                continue;
            }
            if (substitutingTeachers.containsValue(teacher.getValue())) {
                continue;
            }
            unoccupiedTeachers.put(i, teacher.getValue());
            i++;
        }
        
        return unoccupiedTeachers;
    }
    
    private static synchronized Map<Integer, contents.entities.Teacher> selectTeachingTeachers(int day, int time) throws SQLException {
        Map<Integer, contents.entities.Teacher> teachingTeachers = new HashMap<Integer, contents.entities.Teacher>();
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TEACHING_TEACHERS);
        preparedStatement.setInt(1, day);
        preparedStatement.setInt(2, time);
        ResultSet resultSet = preparedStatement.executeQuery();  
        int i = 1;
        while (resultSet.next()){
            contents.entities.Teacher teacher = Database.getTeacher(resultSet.getInt(1));
            teachingTeachers.put(i, teacher);
            i++;
        }
        resultSet.close();
        preparedStatement.close();
        
        return teachingTeachers;
    }
    
    private static synchronized Map<Integer, contents.entities.Teacher> selectMissingTeachers(java.util.Date date, int time) throws SQLException {
        Map<Integer, contents.entities.Teacher> missingTeachers = new HashMap<Integer, contents.entities.Teacher>();
        
        java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
        String dateString = dateFormat.format(date);
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_MISSING_TEACHERS);
        preparedStatement.setString(1, dateString);
        preparedStatement.setInt(2, time);
        preparedStatement.setInt(3, time);
        ResultSet resultSet = preparedStatement.executeQuery();  
        int i = 1;
        while (resultSet.next()){
            contents.entities.Teacher teacher = Database.getTeacher(resultSet.getInt(1));
            missingTeachers.put(i, teacher);
            i++;
        }
        resultSet.close();
        preparedStatement.close();
    
        return missingTeachers;
    }
    
    private static synchronized Map<Integer, contents.entities.Teacher> selectSubstitutingTeachers(java.util.Date date, int time) throws SQLException {
        Map<Integer, contents.entities.Teacher> substitutingTeachers = new HashMap<Integer, contents.entities.Teacher>();
        
        java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
        String dateString = dateFormat.format(date);
        
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_SUBSTITUTORS_IN_DATE_AND_TIME);
        preparedStatement.setString(1, dateString);
        preparedStatement.setInt(2, time);
        ResultSet resultSet = preparedStatement.executeQuery();
        int i = 1;
        while (resultSet.next()) {
            contents.entities.Teacher teacher = Database.getTeacher(resultSet.getInt(1));
            substitutingTeachers.put(i, teacher);
            i++;
        }
        resultSet.close();
        preparedStatement.close();
        
        return substitutingTeachers;
    }
    
    public static synchronized void setSubstitution(int absenceId, int timetableId, contents.entities.Teacher substitutor) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_INSERT_SUBSTITUTION);
        preparedStatement.setInt(1, absenceId);
        preparedStatement.setInt(2, timetableId);
        if (substitutor == null) {
            preparedStatement.setString(3, null);
        } else {
            preparedStatement.setInt(3, substitutor.getTeacherId());
        }
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
    
    public static synchronized contents.entities.SubstitutionToSolve getSubstitutionToSolve(int timetableId, int absenceId) throws SQLException {
        contents.entities.Timetable timetable = Database.getTimetable(timetableId);
        contents.entities.Absence absence = Database.getAbsence(absenceId);

        return new contents.entities.SubstitutionToSolve(timetable, absence);
    }
    
    public static synchronized Map<Integer, contents.entities.SubstitutionToSolve> selectSubstitutionsToSolveSinceDate(java.util.Date date) throws SQLException {        
        Map<Integer, contents.entities.SubstitutionToSolve> substitutionsToSolve = new HashMap<Integer, contents.entities.SubstitutionToSolve>();
        
        // pairs of all corresponding absenceId, timetableId
        Map<Integer, List<Integer> > absenceTimetablePairs = new HashMap<Integer, List<Integer> >();
        
        // get all absences since date
        Map<Integer, contents.entities.Absence> allAbsences = Database.selectAllAbsencesSinceDate(date);
        for (Map.Entry<Integer, contents.entities.Absence> absence : allAbsences.entrySet()) {
            int dayOfWeek = absence.getValue().getDate().getDay();
            if (dayOfWeek < 1 || dayOfWeek > 5) {
                continue;
            }
            
            PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TIMETABLE_ID_WHERE_TEACHER_DAY_AND_UNIT_BETWEEN);
            preparedStatement.setInt(1, absence.getValue().getMissingTeacher().getTeacherId());
            preparedStatement.setInt(2, dayOfWeek);
            preparedStatement.setInt(3, absence.getValue().getFromUnit());
            preparedStatement.setInt(4, absence.getValue().getToUnit());
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                if (!absenceTimetablePairs.containsKey(absence.getValue().getAbsenceId())) {
                    absenceTimetablePairs.put(absence.getValue().getAbsenceId(), new ArrayList<Integer>());
                }
                
                absenceTimetablePairs.get(absence.getValue().getAbsenceId()).add(resultSet.getInt(1));
            }
            resultSet.close();
            preparedStatement.close();
        }
        
        // get all substitutionsToSolve, even when they are solved already
        List<contents.entities.SubstitutionToSolve> allSubstitutionsToSolve = new ArrayList<contents.entities.SubstitutionToSolve>();
        for (Map.Entry<Integer, List<Integer> > entry : absenceTimetablePairs.entrySet()) {
            int absenceId = entry.getKey();
            List<Integer> timetableIds = entry.getValue();
            
            for (int i = 0; i < timetableIds.size(); i++) {
                int timetableId = timetableIds.get(i);
                contents.entities.SubstitutionToSolve substitutionToSolve = Database.getSubstitutionToSolve(timetableId, absenceId);
                allSubstitutionsToSolve.add(substitutionToSolve);
            }
        }
        
        if (allSubstitutionsToSolve.isEmpty()) {
            return substitutionsToSolve;
        }
        Collections.sort(allSubstitutionsToSolve, new SubstitutionToSolve.ByDateClassNameAndTimeComparator());
        
        // get already defined (solved) substitutions
        List<DatabaseEntities.Substitution> substitutions = Database.selectSubstitutionsSinceDate(date);
        
        // get only substitutionsToSolve, which are not solved yet
        int j = 1;
        for (int i = 0; i < allSubstitutionsToSolve.size(); i++) {
            contents.entities.SubstitutionToSolve substitutionToSolve = allSubstitutionsToSolve.get(i);
            boolean isNotSolved = true;
            
            for (int k = 0; k < substitutions.size(); k++) {
                PreparedStatement preparedStatement = connection.prepareStatement(DatabaseQueries.QUERY_SELECT_TIMETABLE_ID_AND_ABSENCE_ID_OF_SUBSTITUTION);
                preparedStatement.setInt(1, substitutions.get(k).substitutionId);
                ResultSet resultSet = preparedStatement.executeQuery();
                resultSet.next();
                int timetableId = resultSet.getInt(1);
                int absenceId = resultSet.getInt(2);
                resultSet.close();
                preparedStatement.close();

                if (substitutionToSolve.getAbsence().getAbsenceId() == absenceId &&
                        substitutionToSolve.getTimetable().getTimetableId() == timetableId) {
                    isNotSolved = false;
                    break;
                }
            }
            
            if (isNotSolved) {
                substitutionsToSolve.put(j, substitutionToSolve);
                j++;
            }
        }
        
        return substitutionsToSolve;
    }
}
